package com.neusoft.weekreport.component.bizService.impl;

import com.neusoft.weekreport.component.entities.SendExcelUserInfo;
import com.neusoft.weekreport.component.req.GetExportExcelReq;
import com.neusoft.weekreport.mapper.ExportExcelMapper;
import com.neusoft.weekreport.mapper.SendExcelMailMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class ExportExcelService {

    @Resource
    ExportExcelMapper exprotExcelMapper;
    @Resource
    SendExcelMailMapper sendExcelMailMapper;

    //日报导出
    public void exportDayInfo(GetExportExcelReq req, HttpServletResponse response) throws Exception{
        List<SendExcelUserInfo> userInfos;
        //判断是组还是部门
        if (("10001".equals(req.getGroupid()) || "20001".equals(req.getGroupid())) && !"f93ac2b4-001f-4847-972d-7e986e59f17a".equals(req.getId())){
            userInfos =  exprotExcelMapper.selectByDept(req);
        }else if("f93ac2b4-001f-4847-972d-7e986e59f17a".equals(req.getId()) || "6b543480-f400-4190-9219-e92766fd2c92".equals(req.getId())){
            userInfos = exprotExcelMapper.selectByDay(req);
        } else {
            userInfos =  exprotExcelMapper.selectByGroup(req);
        }
        for (SendExcelUserInfo userInfo : userInfos){
            req.setUserName(userInfo.getUserName());
            List<Map<String,String>> projbctName = exprotExcelMapper.selectByProject(req);
            userInfo.setProjbctName(projbctName);
            List<Map<String,String>> workContent = exprotExcelMapper.selectByTask(req);
            userInfo.setWorkContent(workContent);
            List<Map<String,String>> delivbrables = exprotExcelMapper.selectByResult(req);
            userInfo.setDelivbrables(delivbrables);
            List<Map<String,String>> dictValue = exprotExcelMapper.selectByWay(req);
            userInfo.setDictValue(dictValue);
            String time = exprotExcelMapper.selectByTime(req);
            userInfo.setLaborHour(time);
        }
        //生成Excel流文件返回给前端
        DateFormat df1 = DateFormat.getDateInstance();
        String fileName = "云医疗事业部-"+df1.format(new Date())+"日报.xlsx";//文件名称
        response.setContentType("application/vnd.ms-excel;charset = utf-8");//将结果导出为Excel
        response.setHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes(),"ISO8859-1"));//将文件作为附件下载
        XSSFWorkbook workbook = writeExcelInfo(userInfos);
        workbook.write(response.getOutputStream());
    }

    public XSSFWorkbook writeExcelInfo(List<SendExcelUserInfo> userInfos) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();//创建Excel工作簿对象
        XSSFSheet sheet = workbook.createSheet();//在工作簿中创建工作表对象
        DateFormat df1 = DateFormat.getDateInstance();
        sheet.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true
        //设置列宽度
        sheet.setColumnWidth(0,8*256);
        sheet.setColumnWidth(1,20*256);
        sheet.setColumnWidth(2,21*256);
        sheet.setColumnWidth(3,20*256);
        sheet.setColumnWidth(4,10*256);
        sheet.setColumnWidth(5,11*256);
        sheet.setColumnWidth(6,22*256);
        sheet.setColumnWidth(7,40*256);
        sheet.setColumnWidth(8,30*256);
        sheet.setColumnWidth(9,15*256);
        sheet.setColumnWidth(10,7*256);
        workbook.setSheetName(0,df1.format(new Date())+"日报");//设置工作表的名称


        int colNum=0;
        //工作明细

        colNum = setWeekDetailExcel( workbook, sheet,colNum ,userInfos);
        return workbook;
    }
    //循环填充工作明细内容
    public int setWeekDetailExcel(XSSFWorkbook workbook, XSSFSheet sheet, int colNum , List<SendExcelUserInfo> userInfos){
        Row row02 = sheet.createRow(colNum);
        row02.setHeight((short) (27 * 20));
        Cell cell21 = row02.createCell(0);
        cell21.setCellStyle(setStyleWeekDetailColunm(workbook,177,160,199));
        cell21.setCellValue("姓名");
        Cell cell22 = row02.createCell(1);
        cell22.setCellStyle(setStyleWeekDetailColunm(workbook,177,160,199));
        cell22.setCellValue("邮件");
        Cell cell23 = row02.createCell(2);
        cell23.setCellStyle(setStyleWeekDetailColunm(workbook,177,160,199));
        cell23.setCellValue("二级部门");
        Cell cell24 = row02.createCell(3);
        cell24.setCellStyle(setStyleWeekDetailColunm(workbook,177,160,199));
        cell24.setCellValue("三级部门");
        Cell cell25 = row02.createCell(4);
        cell25.setCellStyle(setStyleWeekDetailColunm(workbook,177,160,199));
        cell25.setCellValue("直接负责人");
        Cell cell26 = row02.createCell(5);
        cell26.setCellStyle(setStyleWeekDetailColunm(workbook,177,160,199));
        cell26.setCellValue("编制");
        Cell cell28 = row02.createCell(6);
        cell28.setCellStyle(setStyleWeekDetailColunm(workbook,255,192,0));
        cell28.setCellValue("项目"+"\r\n"+"（没有具体项目就空着）");
        Cell cell29 = row02.createCell(7);
        cell29.setCellStyle(setStyleWeekDetailColunm(workbook,255,192,0));
        cell29.setCellValue("工作内容");
        Cell cell30 = row02.createCell(8);
        cell30.setCellStyle(setStyleWeekDetailColunm(workbook,255,192,0));
        cell30.setCellValue("成果物");
        Cell cell31 = row02.createCell(9);
        cell31.setCellStyle(setStyleWeekDetailColunm(workbook,255,192,0));
        cell31.setCellValue("支持方式");
        Cell cell32 = row02.createCell(10);
        cell32.setCellStyle(setStyleWeekDetailColunm(workbook,255,192,0));
        cell32.setCellValue("工时"+"\r\n"+"（小时）");
        ++colNum;

        //日报具体信息
        for (SendExcelUserInfo userInfo : userInfos) {

            Row row03 = sheet.createRow(colNum);
            Cell cell33 = row03.createCell(0);
            cell33.setCellStyle(setStyleWeekDetailContent(workbook));
            cell33.setCellValue(userInfo.getUserName());

            Cell cell34 = row03.createCell(1);
            cell34.setCellStyle(setStyleWeekDetailContent(workbook));
            cell34.setCellValue(userInfo.getEmailAddress());

            Cell celli35 = row03.createCell(2);
            celli35.setCellStyle(setStyleWeekDetailContent(workbook));
            celli35.setCellValue(userInfo.getFristGroup())
            ;
            Cell celli36 = row03.createCell(3);
            celli36.setCellStyle(setStyleWeekDetailContent(workbook));
            celli36.setCellValue(userInfo.getGroupName());

            Cell celli37 = row03.createCell(4);
            celli37.setCellStyle(setStyleWeekDetailContent(workbook));
            celli37.setCellValue(userInfo.getDescription());

            Cell celli38 = row03.createCell(5);
            celli38.setCellStyle(setStyleWeekDetailContent(workbook));
            celli38.setCellValue(userInfo.getUserKind());

            Cell celli39 = row03.createCell(6);
            celli39.setCellStyle(setStyleDayDetailContent(workbook));
            String b = "";
            int bb = 1;
            for(Map<String,String> dataMap : userInfo.getProjbctName()){
                String a = "";
                if(userInfo.getProjbctName().size() != bb ){
                    if(String.valueOf(dataMap.get("projbctName")).equals("") && String.valueOf(dataMap.get("projbctName")) == "null"){
                        a = "";
                    }else if(!String.valueOf(dataMap.get("projbctName")).equals("") && String.valueOf(dataMap.get("projbctName")) != "null" ){
                        a = dataMap.get("projbctName") + ";\r\n";
                    }
                }else{
                    if(String.valueOf(dataMap.get("projbctName")).equals("") && String.valueOf(dataMap.get("projbctName")) == "null"){
                        a = "";
                    }else if(!String.valueOf(dataMap.get("projbctName")).equals("") && String.valueOf(dataMap.get("projbctName")) != "null"){
                        a = dataMap.get("projbctName") + ";";
                    }
                }
                b+=a;
                bb++;
            }
            celli39.setCellValue(b);

            Cell celli40 = row03.createCell(7);
            celli40.setCellStyle(setStyleDayDetailContent(workbook));
            String e = "";
            int aa = 1;
            for (Map<String,String> dataMap : userInfo.getWorkContent()){
                String a;
                if (userInfo.getWorkContent().size() != aa){
                    a =  String.valueOf(aa) + "、" + dataMap.get("workContent") +";\r\n";
                }else {
                    a =  String.valueOf(aa) + "、" + dataMap.get("workContent") +";";
                }
                e+=a;
                aa++;
            }
            celli40.setCellValue(e);

            Cell celli41 = row03.createCell(8);
            String c = "";
            int cc = 1;
            for(Map<String,String> dataMap : userInfo.getDelivbrables()){
                String a = "";
                if(userInfo.getDelivbrables().size() != cc ){
                    if(String.valueOf(dataMap.get("delivbrables")).equals("") && String.valueOf(dataMap.get("delivbrables")) == "null"){
                        a = "";
                    }else if(!String.valueOf(dataMap.get("delivbrables")).equals("") && String.valueOf(dataMap.get("delivbrables")) != "null" ){
                        a = String.valueOf(cc) + "、" + dataMap.get("delivbrables") + ";\r\n";
                    }
                }else{
                    if(String.valueOf(dataMap.get("delivbrables")).equals("") && String.valueOf(dataMap.get("delivbrables")) == "null"){
                        a = "";
                    }else if(!String.valueOf(dataMap.get("delivbrables")).equals("") && String.valueOf(dataMap.get("delivbrables")) != "null"){
                        a = String.valueOf(cc) + "、" + dataMap.get("delivbrables") + ";";
                    }
                }
                c+=a;
                cc++;
            }
            celli41.setCellStyle(setStyleDayDetailContent(workbook));
            celli41.setCellValue(c);

            Cell celli42 = row03.createCell(9);
            celli42.setCellStyle(setStyleDayDetailContent(workbook));
            String d = "";
            int dd = 1;
            for(Map<String,String> dataMap : userInfo.getDictValue()){
                String a;
                if(userInfo.getDictValue().size() != dd){
                    a = dataMap.get("dictValue") + ";\r\n";
                }else{
                    a = dataMap.get("dictValue") + ";";
                }
                d+=a;
                dd++;
            }
            celli42.setCellValue(d);

            Cell celli43 = row03.createCell(10);
            celli43.setCellStyle(setStyleDayDetailContent(workbook));
            celli43.setCellValue(userInfo.getLaborHour());
            ++colNum;
        }
        return colNum;
    }

    //设置日报明细表头样式
    public XSSFCellStyle setStyleWeekDetailColunm(XSSFWorkbook workbook, int a, int b, int c){

        //设置单元格字体
        XSSFCellStyle cellStyle = workbook.createCellStyle();//用于设置单元格的显示样式

        XSSFColor xssfColor= new XSSFColor(new java.awt.Color(a,b,c));
        cellStyle.setFillForegroundColor(xssfColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中

        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFFont font = workbook.createFont();//创建字体对象
        font.setFontHeightInPoints((short) 10);//设置字号大小
        font.setFontName("微软雅黑");//设置字体
        font.setBold(true);//设置粗体
        cellStyle.setFont(font);//将字体设置添加到单元格样式中。

        cellStyle.setWrapText(true);
        return cellStyle;
    }

    //设置日报明细内容样式
    public XSSFCellStyle setStyleWeekDetailContent(XSSFWorkbook workbook){
        //设置单元格字体
        XSSFCellStyle cellStyle = workbook.createCellStyle();//用于设置单元格的显示样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFFont font = workbook.createFont();//创建字体对象
        font.setFontHeightInPoints((short) 10);//设置字号大小
        font.setFontName("微软雅黑");//设置字体
        cellStyle.setFont(font);//将字体设置添加到单元格样式中。
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    //设置日报明细内容样式
    public XSSFCellStyle setStyleDayDetailContent(XSSFWorkbook workbook){
        //设置单元格字体
        XSSFCellStyle cellStyle = workbook.createCellStyle();//用于设置单元格的显示样式
        cellStyle.setVerticalAlignment(VerticalAlignment.TOP);//顶端对齐
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFFont font = workbook.createFont();//创建字体对象
        font.setFontHeightInPoints((short) 10);//设置字号大小
        font.setFontName("微软雅黑");//设置字体
        cellStyle.setFont(font);//将字体设置添加到单元格样式中。
        cellStyle.setWrapText(true);
        return cellStyle;
    }

}